import openpyxl
import win32com.client

from tools_pmc import (fill_blue, fill_yellow, fill_orange,
                       border_gray,
                       font_ERP,
                       today_str1, yesterday_str2, yesterday_str1, str1, str2,
                       alignment_center, alignment_left, alignment_right,
                       vlookup, vlookup_two, if_in_BOM,
                       lj_wlxxb, lj_tyl1, lj_tyl2, lj_tyl, lj_bom, lj_last_in, lj_last_out,
                       ws_wlxxb,
                       table_array_wlxxb
                       )

wb_lin = openpyxl.load_workbook(lj_last_in)
ws_lin = wb_lin.worksheets[0]
table_array_lin = list(ws_lin['A2':'E' + str(ws_lin.max_row)])

wb_lout = openpyxl.load_workbook(lj_last_out)
ws_lout = wb_lout.worksheets[0]
table_array_lout = list(ws_lout['A2':'D' + str(ws_lout.max_row)])

wb_bom = openpyxl.load_workbook(lj_bom)  # 配套使用
ws_bom = wb_bom.worksheets[0]
day = 365
lj = ("//192.168.70.101/19计划管理部/01.计划/5，物控管理/2，呆滞料管理/呆滞数据整理" + today_str1 + "/file/分析结果-" +
      str(day) + "-" + today_str1 + '.xlsx')
wb = openpyxl.load_workbook(lj)
ws = wb.worksheets[0]

ws.insert_rows(1, 1)
ws.delete_cols(4, 1)
ws.insert_cols(5, 2)
ws.insert_cols(8, 2)
for i in range(2, ws.max_row + 1):
    ws.cell(i, 2).value = vlookup(int(ws.cell(i, 1).value), table_array_wlxxb, 2)
    ws.cell(i, 3).value = vlookup(int(ws.cell(i, 1).value), table_array_wlxxb, 3)
    temp1 = ws.cell(i, 4).value.find("（") + 1
    temp2 = ws.cell(i, 4).value.find("天")
    ws.cell(i, 5).value = int(ws.cell(i, 4).value[temp1:temp2])
    ws.cell(i, 8).value = if_in_BOM(ws_bom, int(ws.cell(i, 1).value))
    ws.cell(i, 9).value = ws.cell(i, 11).value
    temp1 = vlookup(int(ws.cell(i, 1).value), table_array_wlxxb, 12)
    temp2 = vlookup(int(ws.cell(i, 1).value), table_array_wlxxb, 9)
    ws.cell(i, 11).value = temp2 - temp1
    ws.cell(i, 12).value = ws.cell(i, 10).value - ws.cell(i, 11).value
    ws.cell(i, 13).value = vlookup(int(ws.cell(i, 1).value), table_array_wlxxb, 14)  # 可用量
    ws.cell(i, 14).value = vlookup(int(ws.cell(i, 1).value), table_array_wlxxb, 19) - \
                           vlookup(int(ws.cell(i, 1).value), table_array_wlxxb, 20)  # 在途
    ws.cell(i, 15).value = vlookup(int(ws.cell(i, 1).value), table_array_wlxxb, 20)  # 在检数量
    ws.cell(i, 16).value = vlookup(int(ws.cell(i, 1).value), table_array_wlxxb, 21)  # 已请未购量
    ws.cell(i, 17).value = vlookup(int(ws.cell(i, 1).value), table_array_wlxxb, 24)  # 固定前置天数

    ws.cell(i, 18).value = vlookup(int(ws.cell(i, 1).value), table_array_lin, 2)  # 最后请购
    ws.cell(i, 19).value = vlookup(int(ws.cell(i, 1).value), table_array_lin, 3)  # 最后请购
    ws.cell(i, 20).value = vlookup(int(ws.cell(i, 1).value), table_array_lin, 4)  # 最后请购
    ws.cell(i, 21).value = vlookup(int(ws.cell(i, 1).value), table_array_lin, 5)  # 最后请购

    ws.cell(i, 22).value = vlookup(int(ws.cell(i, 1).value), table_array_lout, 2)  # 最后出库
    ws.cell(i, 23).value = vlookup(int(ws.cell(i, 1).value), table_array_lout, 3)  # 最后出库
    ws.cell(i, 24).value = vlookup(int(ws.cell(i, 1).value), table_array_lout, 4)  # 最后出库

temp1 = 0
for i in range(2, ws.max_row + 2):
    if ws.cell(i, 1).value == ws.cell(i - 1, 1).value:
        temp1 += ws.cell(i, 5).value
    else:
        ws.cell(i - 1, 6).value = temp1
        temp1 = ws.cell(i, 5).value

for i in range(ws.max_row, 1, -1):
    if ws.cell(i, 6).value is None:
        ws.cell(i, 6).value = ws.cell(i + 1, 6).value

list1 = ["品号", "品名", "规格型号", "呆滞周期（未出库周期）", "呆滞天数", "呆滞天数合计", "周期结束原因", "BOM使用",
         "周期内入库数量", "周期内呆滞数量", "不良及盘亏数量", "呆滞数量（减盘亏不良）", "可用数量", "在途数量",
         "在检数量", "已请未购数量", "固定前置天数", "最后请购日期", "最后请购数量", "请购人", "请购备注",
         "最后出库日期",
         "最后出库数量", "出库对象"]
list2 = [14, 30, 30, 34, 7, 7, 25, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 12, 7, 7, 7, 12, 7, 7, 7, 7, 7, 7]
for i in range(1, 25):
    ws.column_dimensions[openpyxl.utils.get_column_letter(i)].width = list2[i - 1]
    ws.cell(1, i).value = list1[i - 1]

wb.save("//192.168.70.101/19计划管理部/01.计划/5，物控管理/2，呆滞料管理/呆滞数据整理" + today_str1 + "/分析结果-" +
        str(day) + "-" + today_str1 + '.xlsx')
